package com.bbb;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;

public class StudentSet {
	
	public static void main (String[] args){
		StudentSet ss = new StudentSet();
		ss.deleteDate("5");
	}
	
	public void insertStudent(String name,String sex,String address,String maddress,String brithday){
		Statement st = null;
		Connection con = null;
		StringBuffer sb = new StringBuffer();
		sb.append("insert into tb_student (name,sex,brithday,address,maddress) value ('");
		sb.append(name);
		sb.append("','");
		sb.append(sex);
		sb.append("','");
		sb.append(brithday);
		sb.append("','");
		sb.append(address);
		sb.append("','");
		sb.append(maddress);
		sb.append("')");
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			st.executeUpdate(sb.toString());
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
		
	}

	public List<Map<String, String>> selectDate(){
		Statement st = null;
		Connection con = null;
		ResultSet rs = null;
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			StringBuffer sb = new StringBuffer();
			sb.append("select tb_student_id,name,sex,brithday,address,maddress from tb_student ");
	
			rs = st.executeQuery(sb.toString());
			
			while (rs.next()) 
			{
				Map<String, String> map = new HashMap<String, String>();
				String idString = rs.getString("tb_student_id");
				map.put("tb_student_id", idString);
				String nameString = rs.getString("name");
				map.put("name", nameString);
				String sexString = rs.getString("sex");
				map.put("sex", sexString);
				String addressString = rs.getString("address");
				map.put("address", addressString);
				String brithdayString = rs.getString("brithday");
				map.put("brithday", brithdayString);
				String maddressString = rs.getString("maddress");
				map.put("maddress", maddressString);
				list.add(map);
			}
			return list;
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			return null;
		}
		
	}
	public List<Map<String, String>> selectInfo(String name,String sex,String address,String maddress,String maddressarea,String brithday){
		Statement st = null;
		Connection con = null;
		ResultSet rs = null;
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		StringBuffer sb = null;
		StringBuffer sb2 = null;
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			sb = new StringBuffer();
			sb.append("select tb_student_id,name,sex,brithday,address,maddress from tb_student ");
			
			sb2 = new StringBuffer();
			if (StringUtils.isNotBlank(name)) 
			{
				sb2.append(" name = '");
				sb2.append(name);
				sb2.append("' ");
			}
			if (StringUtils.isNotBlank(sex)) 
			{
				if (StringUtils.isNotBlank(sb2.toString())) 
				{
					sb2.append(" and ");
				}
				sb2.append("sex = '");
				sb2.append(sex);
				sb2.append("' ");
			}
			if (StringUtils.isNotBlank(address)) 
			{
				if (StringUtils.isNotBlank(sb2.toString())) 
				{
					sb2.append(" and ");
				}
				sb2.append("address = '");
				sb2.append(address);
				sb2.append("' ");
			}
			if (StringUtils.isNotBlank(brithday)) 
			{
				if (StringUtils.isNotBlank(sb2.toString())) 
				{
					sb2.append(" and ");
				}
				sb2.append("brithday = '");
				sb2.append(brithday);
				sb2.append("' ");
			}
			if (StringUtils.isNotBlank(maddress)&&StringUtils.isNotBlank(maddressarea)) 
			{
				if (StringUtils.isNotBlank(sb2.toString())) 
				{
					sb2.append(" and ");
				}
				String mailaddress = maddress + "@" + maddressarea;
				sb2.append("maddress = '");
				sb2.append(mailaddress);
				sb2.append("' ");
			}
			if (StringUtils.isNotBlank(sb2.toString())) 
			{
				sb.append(" where ");
				sb.append(sb2.toString());
			}
				
			System.out.print(sb.toString());
			rs = st.executeQuery(sb.toString());
			
			while (rs.next()) 
			{
				Map<String, String> map = new HashMap<String, String>();
				String idString = rs.getString("tb_student_id");
				map.put("tb_student_id", idString);
				String nameString = rs.getString("name");
				map.put("name", nameString);
				String sexString = rs.getString("sex");
				map.put("sex", sexString);
				String addressString = rs.getString("address");
				map.put("address", addressString);
				String brithdayString = rs.getString("brithday");
				map.put("brithday", brithdayString);
				String maddressString = rs.getString("maddress");
				map.put("maddress", maddressString);
				list.add(map);
			}
			return list;
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			return null;
		}
	}

	public void changeDate(String id,String name,String sex,String address,String maddress,String brithday) 
	{
		Statement st = null;
		Connection con = null;
		int sure_id = Integer.parseInt(id);
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			
			StringBuffer sb = new StringBuffer();
			sb.append("update tb_student set name = '");
			sb.append(name);
			sb.append("', sex = '");
			sb.append(sex);
			sb.append("', address = '");
			sb.append(address);
			sb.append("', brithday = '");
			sb.append(brithday);
			sb.append("', maddress = '");
			sb.append(maddress);
			sb.append("' where tb_student_id = ");
			sb.append(sure_id);
			
			st.executeUpdate(sb.toString());
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
	
	public Map<String, String> makesureDate(String id) {
		Statement st = null;
		Connection con = null;
		ResultSet rs = null; 
		Map<String, String> map = new HashMap<String, String>();
		int idnum = Integer.parseInt(id);
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			
			StringBuffer sb = new StringBuffer();
			sb.append("select name,address,sex,maddress,brithday from tb_student where tb_student_id = ");
			sb.append(idnum);
			
			rs = st.executeQuery(sb.toString());
			if (rs.next()) {
				String name = rs.getString("name");
				map.put("name", name);
				String address = rs.getString("address");
				map.put("address", address);
				String sex = rs.getString("sex");
				map.put("sex", sex);
				String brithday = rs.getString("brithday");
				map.put("brithday", brithday);
				String maddress_temp = rs.getString("maddress");
				int index = maddress_temp.indexOf("@");
				String maddress = maddress_temp.substring(0, index);
				String maddressarea = maddress_temp.substring(index+1);
				map.put("maddress", maddress);
				map.put("maddressarea", maddressarea);
			}
			return map;
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
			return null;
		}
	}
	
	public void deleteDate(String id) {
		Statement st = null;
		Connection con = null;
		StringBuffer sb = null;
		int student_id = Integer.parseInt(id);
		
		try 
		{
			Class.forName("com.mysql.jdbc.Driver");
			con = DriverManager.getConnection("jdbc:mysql://localhost:3306/pro1", "root", "123456");
			st = con.createStatement();
			
			sb = new StringBuffer();
			
			sb.append("delete from tb_student where tb_student_id = ");
			sb.append(student_id);
			
			System.out.print(sb.toString());
			st.executeUpdate(sb.toString());
		} 
		catch (Exception e) 
		{
			e.printStackTrace();
		}
	}
}
